t	----------------------Bệnh nhân -----------------------
 --Danh Sách Bệnh Nhân
 create proc sp_DanhSachBenhNhan
 as
 begin transaction
	begin try
		select * from BenhNhan
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
 go
 --Danh Sách Bệnh Nhân theo tên
 create proc sp_DanhSachBenhNhanTheoTen
 @TenBN nvarchar(50)
 as
 begin transaction
	begin try
		select * from BenhNhan where TenBN like '%'+@TenBN+'%'
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
 go
 -- Danh sách bênh nhân theo địa chỉ 
 create proc sp_DanhSachBenhNhanTheoDiaChi
 @DiaChi nvarchar(50)
 as
 begin transaction
	begin try
		select * from BenhNhan where DiaChi like '%'+@DiaChi+'%'
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
 go
  -- Danh sách bệnh nhân theo giới tính nam
 create proc sp_DanhSachBenhNhanTheoGioiTinhNam
 @GioiTinh int
 as
 begin transaction
	begin try
		select * from BenhNhan where GioiTinh = 1
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
 go
   -- Danh sách bệnh nhân theo giới tính nu
 create proc sp_DanhSachBenhNhanTheoGioiTinhNu
 @GioiTinh int
 as
 begin transaction
	begin try
		select * from BenhNhan where GioiTinh = 0
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
 go
  --Danh Sách Bệnh Nhân theo mã
 create proc sp_DanhSachBenhNhanTheoMa
 @MaBN nchar(10)
 as
 begin transaction
	begin try
		select * from BenhNhan where MaBN=@MaBN
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
 go
-- Thêm bệnh nhân
create
-- alter
 proc sp_ThemBenhNhan
@TenBN nvarchar(50),
@GioiTinh int,
@CMND nchar(10),
@DiaChi nvarchar(MAX),
@SoDienThoai nvarchar(12),
@NgheNghiep nvarchar(50) ,
@NgaySinh date,
@TheBH int,
@MoTa nvarchar(max),
@Hinh nvarchar(max)
 as
 begin transaction
	begin try	
	declare @ma_next nchar(10) -- max 7 ki tu
	declare @max int
	select @max = count(MaBN) + 1 -- MACV ke tiep dang 'CVxxxxx'
	from dbo.BenhNhan
	where MaBN like 'BN%'
	-- tao maloaive ke tiep
	if(@max >0 and @max <10)
		set @ma_next = 'BN'+ right( '00' + cast(@max as nchar(10)) , 5) 
	if(@max >=10 and @max <100)
		set @ma_next = 'BN'+ right( '0' + cast(@max as nchar(10)) , 5)
	if(@max >=100 and @max <1000)
		set @ma_next = 'BN'+ right( '' + cast(@max as nchar(10)) , 5)
	-- kiem ta ton tai
	while(exists(select MaBN from dbo.BenhNhan where MaBN= @ma_next))
	begin
	set @max = @max + 1
	set @ma_next = 'BN'+ right( '0' + cast(@max as nchar(10)), 5)
    end
		insert into BenhNhan values(@ma_next,@TenBN,@GioiTinh,@CMND,@DiaChi,
		@SoDienThoai,@NgheNghiep,@NgaySinh,@TheBH,@MoTa,@Hinh)
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
go 

-- Xóa Bệnh Nhân
create procedure sp_XoaBenhNhan
@MaBN nchar(10)
as
begin transaction
	begin try
		delete BenhNhan where MaBN=@MaBN
	end try	
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
commit
go
-- Cập nhật Bệnh Nhân 
create procedure sp_CapNhatBenhNhan
@MaBN nchar(10),
@TenBN nvarchar(50),
@GioiTinh int,
@CMND nchar(10),
@DiaChi nvarchar(MAX),
@SoDienThoai nvarchar(12),
@NgheNghiep nvarchar(50) ,
@NgaySinh date,
@TheBH int,
@MoTa nvarchar(max),
@Hinh nvarchar(max)
as
begin transaction
	begin try
		update BenhNhan set TenBN=@TenBN,GioiTinh=@GioiTinh,CMND=@CMND,DiaChi=@DiaChi,
		SoDienThoai=@SoDienThoai,NgheNghiep=@NgheNghiep,NgaySinh=@NgaySinh,TheBH=@TheBH,Mota = @MoTa,Hinh=@Hinh where MaBN=@MaBN
	end try	
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
commit
go

-- Cập nhật chỉ số sức khỏe.
create procedure sp_CapNhatChiSoSucKhoeBenhNhan
@MaBN nchar(10),
@TenBN nvarchar(50),
@GioiTinh int,
@CMND nchar(10),
@DiaChi nvarchar(MAX),
@SoDienThoai nvarchar(12),
@NgheNghiep nvarchar(50) ,
@NgaySinh date,
@TheBH int,
@MoTa nvarchar(max),
@Hinh nvarchar(max)
as
begin transaction
	begin try
		update BenhNhan set TenBN=@TenBN,GioiTinh=@GioiTinh,CMND=@CMND,DiaChi=@DiaChi,
		SoDienThoai=@SoDienThoai,NgheNghiep=@NgheNghiep,NgaySinh=@NgaySinh,TheBH=@TheBH,Mota = @MoTa,Hinh=@Hinh where MaBN=@MaBN
	end try	
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
commit
go
----------------------------------------Hóa đơn dịch vụ-------------------------------
-- Thêm hóa đơn dịch vụ
--select * from HoaDonDichVu
--select * from NhanVien NV
--select * from BenhNhan BN
--delete HoaDonDichVu
--declare @Ma nchar(10)
--exec sp_ThemHDDV '1/3/1992',300,'nv','bn',@Ma output
--select @Ma
--delete HoaDonDichVu where MaHDDichVu = 'hddv'

create
-- alter
 proc sp_ThemHDDV
@NgayLap date,
@ThanhTien money,
@MaNV nchar(10),
@MaBN nchar(10),
@MaHDDV nchar(10) output
 as
 begin transaction
	begin try	
	declare @ma_next nchar(10) -- max 7 ki tu
	declare @max int
	select @max = count(MaHDDichVu) + 1 -- MACV ke tiep dang 'CVxxxxx'
	from dbo.HoaDonDichVu
	where MaHDDichVu like 'HDDV%'
	-- tao maloaive ke tiep
	if(@max >0 and @max <10)
		set @ma_next = 'HDDV'+ right( '00' + cast(@max as nchar(10)) , 5) 
	if(@max >=10 and @max <100)
		set @ma_next = 'HDDV'+ right( '0' + cast(@max as nchar(10)) , 5)
	if(@max >=100 and @max <1000)
		set @ma_next = 'HDDV'+ right( '' + cast(@max as nchar(10)) , 5)
	-- kiem ta ton tai
	while(exists(select MaHDDichVu from dbo.HoaDonDichVu where MaHDDichVu= @ma_next))
	begin
	set @max = @max + 1
	set @ma_next = 'HDDV'+ right( '0' + cast(@max as nchar(10)), 5)
    end
		insert into HoaDonDichVu values(@ma_next,@NgayLap,@ThanhTien,@MaNV,@MaBN)
		set @MaHDDV = @ma_next
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
go 

------------------------------------- Chi tiết hóa đơn dịch vụ-------------------------

--select * from HoaDonDichVu
--select * from DichVu
--select * from ChiTietDichVu
--exec sp_ThemChiTietDV 'HDDV','DV',N'Âm tính không có gì hết'
--delete ChiTietDichVu
create
-- alter
 proc sp_ThemChiTietDV
@MaHDDichVu nchar(10),
@MaDV nchar(10),
@KetQua nvarchar(MAX)
 as
 begin transaction
	begin try	
		insert into ChiTietDichVu values(@MaHDDichVu,@MaDV,@KetQua)
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
go 





----------------------------------------Thuốc------------------------------------------
--Lấy danh sách thuốc
 create proc sp_DanhSachThuoc
 as
 begin transaction
	begin try
		select * from Thuoc
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
 go
-- Lay Danh sách thuốc theo tên
 
 create proc sp_DanhSachThuocTheoTen
 @TenThuoc nvarchar(50)
 as
 begin transaction
	begin try
		select * from Thuoc where TenThuoc = @TenThuoc
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
 go

 
 ----------------------------------------Hóa đơn thuốc-------------------------------
-- Thêm hóa đơn dịch vụ
--select * from HoaDonThuoc
--select * from Thuoc
--select * from BenhNhan 

--delete HoaDonThuoc

declare @Ma nchar(10)
exec sp_ThemHDThuoc '1/3/1992',300,'nv','bn',@Ma output
select @Ma
--delete HoaDonDichVu where MaHDDichVu = 'hddv'


create
-- alter
 proc sp_ThemHDThuoc
@NgayLap date,
@ThanhTien money,
@MaNV nchar(10),
@MaBN nchar(10),
@MaHDThuoc nchar(10) output
 as
 begin transaction
	begin try	
	declare @ma_next nchar(10) -- max 7 ki tu
	declare @max int
	select @max = count(MaHDThuoc) + 1 -- MACV ke tiep dang 'CVxxxxx'
	from dbo.HoaDonThuoc
	where MaHDThuoc like 'HDT%'
	-- tao maloaive ke tiep
	if(@max >0 and @max <10)
		set @ma_next = 'HDT'+ right( '00' + cast(@max as nchar(10)) , 5) 
	if(@max >=10 and @max <100)
		set @ma_next = 'HDT'+ right( '0' + cast(@max as nchar(10)) , 5)
	if(@max >=100 and @max <1000)
		set @ma_next = 'HDT'+ right( '' + cast(@max as nchar(10)) , 5)
	-- kiem ta ton tai
	while(exists(select MaHDThuoc from dbo.HoaDonThuoc where MaHDThuoc= @ma_next))
	begin
	set @max = @max + 1
	set @ma_next = 'HDT'+ right( '0' + cast(@max as nchar(10)), 5)
    end
		insert into HoaDonThuoc values(@ma_next,@NgayLap,@ThanhTien,@MaNV,@MaBN)
		set @MaHDThuoc = @ma_next
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
go 


------------------------------------- Chi tiết thuốc ------------------------------------

--select * from HoaDonThuoc
--select * from thuoc
--select * from ChiTietToaThuoc
--exec sp_ThemChiTietThuoc 'TH1','HDT',123,667,'lô'
select * from BenhNhan
--delete ChiTietToaThuoc
create
-- alter
 proc sp_ThemChiTietThuoc
@MaThuoc nchar(10), 
@MaHDThuoc nchar(10),
@SoLuong int,
@DonGia money,
@ChiDan nvarchar(MAX)
 as
 begin transaction
	begin try	
		insert into ChiTietToaThuoc values(@MaThuoc,@MaHDThuoc,@SoLuong,@DonGia,@ChiDan)
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
go 
 
 
 
 
 
 
 
 ----------------------------Loại Nhân Viên----------------------------------
 --Lấy danh sách loại nhân viên
 create
 --alter
  proc sp_DanhSachLoaiNhanVien
 as
 begin transaction
	begin try
		select * from LoaiNhanVien
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
 go
 
 --Danh sách loại nhân viên theo mã
 create
 --alter
  proc sp_DanhSachLoaiNhanVienTheoMa
  @MaLoaiNV nchar(10)
 as
 begin transaction
	begin try
		select * from LoaiNhanVien where MaLoaiNV =  @MaLoaiNV 
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
 go
 
 --Thêm loại nhân viên
 create proc sp_ThemLoaiNhanVien
 @TenLoai nvarchar(MAX)
 as
 begin transaction
	begin try
		declare @ma_next nchar(10) -- max 7 ki tu
		declare @max int
		select @max = count(MaLoaiNV) + 1 -- MANV ke tiep dang 'CVxxxxx'
		from dbo.LoaiNhanVien
		where MaloaiNV like 'LNV%'
		-- tao maloaive ke tiep
		if(@max >0 and @max <10)
			set @ma_next = 'LNV'+ right( '00' + cast(@max as nchar(10)) , 5) 
		if(@max >=10 and @max <100)
			set @ma_next = 'LNV'+ right( '0' + cast(@max as nchar(10)) , 5)
		if(@max >=100 and @max <1000)
			set @ma_next = 'LNV'+ right( '' + cast(@max as nchar(10)) , 5)
		-- kiem ta ton tai
		while(exists(select MaLoaiNV from dbo.LoaiNhanVien where MaLoaiNV= @ma_next))
		begin
			set @max = @max + 1
			set @ma_next = 'LNV'+ right( '0' + cast(@max as nchar(10)), 5)
		end	
		insert into LoaiNhanVien values(@ma_next,@TenLoai)
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
 go
 
-- Cập nhật loại nhân viên
create
--alter 
 procedure sp_CapNhatLoaiNhanVien
@MaLoaiNV nchar(10),
@TenLoai nvarchar(MAX)
as
begin transaction
	begin try
		update LoaiNhanVien set TenLoai=@TenLoai
	    where MaLoaiNV = @MaLoaiNV
	end try	
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
commit
go


 
 --Xóa loại nhân viên
create procedure sp_XoaLoaiNhanVien
@MaLoaiNV nchar(10)
as
begin transaction
	begin try
		delete LoaiNhanVien where MaLoaiNV = @MaLoaiNV
	end try	
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
commit
go
----------------------------------KHOA------------------------------------------

--Lấy danh sách khoa
 create
 --alter
  proc sp_DanhSachKhoa
 as
 begin transaction
	begin try
		select * from Khoa
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
 go
 
 --Thêm khoa
 create proc sp_ThemKhoa
 @TenKhoa nvarchar(MAX),
 @MoTa nvarchar(MAX)
 as
 begin transaction
	begin try
		declare @ma_next nchar(10) -- max 7 ki tu
		declare @max int
		select @max = count(MaKhoa) + 1 -- MANV ke tiep dang 'CVxxxxx'
		from dbo.Khoa
		where MaKhoa like 'K%'
		-- tao maloaive ke tiep
		if(@max >0 and @max <10)
			set @ma_next = 'K'+ right( '00' + cast(@max as nchar(10)) , 5) 
		if(@max >=10 and @max <100)
			set @ma_next = 'K'+ right( '0' + cast(@max as nchar(10)) , 5)
		if(@max >=100 and @max <1000)
			set @ma_next = 'K'+ right( '' + cast(@max as nchar(10)) , 5)
		-- kiem ta ton tai
		while(exists(select MaKhoa from dbo.Khoa where MaKhoa= @ma_next))
		begin
			set @max = @max + 1
			set @ma_next = 'K'+ right( '0' + cast(@max as nchar(10)), 5)
		end	
		insert into Khoa values(@ma_next,@TenKhoa,@MoTa)
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
 go
 
-- Cập nhật khoa
create
--alter 
 procedure sp_CapNhatKhoa
@MaKhoa nchar(10),
@TenKhoa nvarchar(MAX),
@MoTa nvarchar(MAX)
as
begin transaction
	begin try
		update Khoa set TenKhoa=@TenKhoa,MoTa=@MoTa
	    where MaKhoa = @MaKhoa
	end try	
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
commit
go

 --Xóa khoa
create procedure sp_XoaKhoa
@Makhoa nchar(10)
as
begin transaction
	begin try
		delete Khoa where MaKhoa = @Makhoa
	end try	
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
commit
go

-------------------------------------------Thuốc-------------------------------------------

--Lấy danh sách thuốc
 create
 --alter
  proc sp_DanhSachThuoc
 as
 begin transaction
	begin try
		select * from Thuoc
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
 go

 --Thêm thuốc
 create 
 --alter 
 proc sp_ThemThuoc
 @TenThuoc nvarchar(50),
 @TinhTrang nchar(10),
 @Gia money,
 @NgayHetHan date
 as
 begin transaction
	begin try
		declare @ma_next nchar(10) -- max 7 ki tu
		declare @max int
		select @max = count(MaThuoc) + 1 -- MANV ke tiep dang 'CVxxxxx'
		from dbo.Thuoc
		where MaThuoc like 'TH%'
		-- tao maloaive ke tiep
		if(@max >0 and @max <10)
			set @ma_next = 'TH'+ right( '00' + cast(@max as nchar(10)) , 5) 
		if(@max >=10 and @max <100)
			set @ma_next = 'TH'+ right( '0' + cast(@max as nchar(10)) , 5)
		if(@max >=100 and @max <1000)
			set @ma_next = 'TH'+ right( '' + cast(@max as nchar(10)) , 5)
		-- kiem ta ton tai
		while(exists(select MaThuoc from dbo.Thuoc where MaThuoc= @ma_next))
		begin
			set @max = @max + 1
			set @ma_next = 'TH'+ right( '0' + cast(@max as nchar(10)), 5)
		end	
		insert into Thuoc values(@ma_next,@TenThuoc,@TinhTrang,@Gia,@NgayHetHan)
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
 go
 
-- Cập nhật thuốc
create
--alter 
 procedure sp_CapNhatThuoc
 @MaThuoc nchar(10),
 @TenThuoc nvarchar(50),
 @TinhTrang nchar(10),
 @Gia money,
 @NgayHetHan date
as
begin transaction
	begin try
		update Thuoc set TenThuoc=@TenThuoc,TinhTrang=@TinhTrang,Gia = @Gia,NgayHetHan = @NgayHetHan
	    where MaThuoc = @MaThuoc
	end try	
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
commit
go

 --Xóa Thuốc
create procedure sp_XoaThuoc
@MaThuoc nchar(10)
as
begin transaction
	begin try
		delete Thuoc where MaThuoc = @MaThuoc
	end try	
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
commit
go

--Danh sách thuốc theo tên
create
--ALTER
 proc [dbo].[sp_DanhSachThuocTheoTen]
 @TenThuoc nvarchar(50)
 as
 begin transaction
	begin try
		select * from Thuoc where TenThuoc = @TenThuoc
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
go
-- Kiểm tra thuốc đã tồn tại chưa
create
--ALTER
 proc KiemTraThuocTonTai
 @TenThuoc nvarchar(50),
 @ketqua int output
 as
 begin transaction
	begin try
		if(exists(select TenThuoc from Thuoc where TenThuoc =@TenThuoc))
		begin
		     set @ketqua = 1
		end
		else
		begin
			set @ketqua = 0;
		end
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
go

--create nonclustered index TenThuoc on Thuoc(TenThuoc)
drop index Thuoc.TenThuoc
declare @kq int
exec KiemTraThuocTonTai 'Alaxan', @kq output
select @kq



create
-- ----------------------------------alter chua
 proc sp_ThemHDThuoc
@NgayLap date,
@ThanhTien money,
@MaNV nchar(10),
@MaBN nchar(10)
 as
 begin transaction
	begin try	
	declare @ma_next nchar(10) -- max 7 ki tu
	declare @max int
	select @max = count(MaHDDichVu) + 1 -- MACV ke tiep dang 'CVxxxxx'
	from dbo.HoaDonDichVu
	where MaHDDichVu like 'HDDV%'
	-- tao maloaive ke tiep
	if(@max >0 and @max <10)
		set @ma_next = 'HDDV'+ right( '00' + cast(@max as nchar(10)) , 5) 
	if(@max >=10 and @max <100)
		set @ma_next = 'HDDV'+ right( '0' + cast(@max as nchar(10)) , 5)
	if(@max >=100 and @max <1000)
		set @ma_next = 'HDDV'+ right( '' + cast(@max as nchar(10)) , 5)
	-- kiem ta ton tai
	while(exists(select MaHDDichVu from dbo.HoaDonDichVu where MaHDDichVu= @ma_next))
	begin
	set @max = @max + 1
	set @ma_next = 'HDDV'+ right( '0' + cast(@max as nchar(10)), 5)
    end
		insert into HoaDonDichVu values(@ma_next,@NgayLap,@ThanhTien,@MaNV,@MaBN)
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
go 


--------------------------------Dịch vụ---------------------------------------
--Lấy danh sách dịch vụ
 create
 --alter
  proc sp_DanhSachDichVu
 as
 begin transaction
	begin try
		select * from DichVu
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
 go

--Lấy danh sách dịch vụ theo mã
 create
 --alter
  proc sp_DanhSachDichVuTheoMa
  @MaDV nchar(10)
 as
 begin transaction
	begin try
		select * from DichVu where MaDV = @MaDV
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
 go


 --Thêm dịch vụ
 create 
 --alter 
 proc sp_ThemDichVu
 @TendichVu nvarchar(MAX),
 @Gia money
 as
 begin transaction
	begin try
		declare @ma_next nchar(10) -- max 7 ki tu
		declare @max int
		select @max = count(MaDV) + 1 -- MANV ke tiep dang 'CVxxxxx'
		from dbo.DichVu
		where MaDV like 'DV%'
		-- tao maloaive ke tiep
		if(@max >0 and @max <10)
			set @ma_next = 'DV'+ right( '00' + cast(@max as nchar(10)) , 5) 
		if(@max >=10 and @max <100)
			set @ma_next = 'DV'+ right( '0' + cast(@max as nchar(10)) , 5)
		if(@max >=100 and @max <1000)
			set @ma_next = 'DV'+ right( '' + cast(@max as nchar(10)) , 5)
		-- kiem ta ton tai
		while(exists(select MaDV from dbo.DichVu where MaDV= @ma_next))
		begin
			set @max = @max + 1
			set @ma_next = 'DV'+ right( '0' + cast(@max as nchar(10)), 5)
		end	
		insert into DichVu values(@ma_next,@TendichVu,@Gia)
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
 go
 
-- Cập nhật dịch vụ
create
--alter 
 procedure sp_CapNhatDichVu
 @MaDV nchar(10),
 @TendichVu nvarchar(MAX),
 @Gia money
as
begin transaction
	begin try
		update DichVu set TenDichVu=@TendichVu,Gia=@Gia
	    where MaDV = @MaDV
	end try	
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
commit
go

 --Xóa dịch vụ
create procedure sp_XoaDichVu
@MaDV nchar(10)
as
begin transaction
	begin try
		delete DichVu where MaDV = @MaDV
	end try	
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
commit
go


----------------------------------------Hồ sơ bệnh án -----------------------------------

--Danh sách hồ sơ bệnh án
create
 --alter
  proc sp_DanhSachHoSoBenhAn
  @MaBN nchar(10)
 as
 begin transaction
	begin try
		select MaHoSo,NgayLap,ChuanDoan,ChiDinh,TrieuChung,MaBN,nv.MaNV from HoSoBenhAn h, NhanVien nv where nv.MaNV = h.MaNV and MaBN =@MaBN 
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
 go

-- Danh sách hồ sơ bệnh án theo mã
create
 --alter
  proc sp_DanhSachHoSoBenhAnTheoMa
  @MaHoSo nchar(10)
 as
 begin transaction
	begin try
		select * from HoSoBenhAn where MaHoSo = @MaHoSo
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
 go



-- Cập nhật hồ sơ bệnh án
create
--alter 
 procedure sp_CapNhatHoSoBA
 @MaHoSo nchar(10),
 @NgayLap date,
 @ChuanDoan nvarchar(Max),
 @ChiDinh nvarchar(Max),
 @TrieuChung nvarchar(Max),
 @MaNV nchar(10)
as
begin transaction
	begin try
		update HoSoBenhAn set NgayLap=@NgayLap,ChuanDoan=@ChuanDoan,
		ChiDinh =@ChiDinh,TrieuChung=@TrieuChung,MaNV =@MaNV
	    where MaHoSo =@MaHoSo
	end try	
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
commit
go
--Thêm hồ sơ bệnh án

 create
 --alter
  proc sp_ThemHoSoBenhAn
  @NgayLap date,
  @ChuanDoan nvarchar(Max),
  @ChiDinh nvarchar(Max),
  @TrieuChung nvarchar(Max),
  @MaBN nchar(10),
  @MaNV nchar(10)
 as
 begin transaction
	begin try
		declare @ma_next nchar(10) -- max 7 ki tu
		declare @max int
		select @max = count(MaHoSo) + 1 -- MANV ke tiep dang 'CVxxxxx'
		from dbo.HoSoBenhAn
		where MaHoSo like 'HSBA%'
		-- tao maloaive ke tiep
		if(@max >0 and @max <10)
			set @ma_next = 'HSBA'+ right( '00' + cast(@max as nchar(10)) , 5) 
		if(@max >=10 and @max <100)
			set @ma_next = 'HSBA'+ right( '0' + cast(@max as nchar(10)) , 5)
		if(@max >=100 and @max <1000)
			set @ma_next = 'HSBA'+ right( '' + cast(@max as nchar(10)) , 5)
		-- kiem ta ton tai
		while(exists(select MaHoSo from dbo.HoSoBenhAn where MaHoSo= @ma_next))
		begin
			set @max = @max + 1
			set @ma_next = 'HSBA'+ right( '0' + cast(@max as nchar(10)), 5)
		end	
		insert into HoSoBenhAn values(@ma_next,@NgayLap,@ChuanDoan,@ChiDinh,@TrieuChung,@MaBN,@MaNV)
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
 go

 --Xóa benh an
create
--alter
procedure sp_XoaHoSoBenhAn
@MaHoSo nchar(10)
as
begin transaction
	begin try
		delete HoSoBenhAn where  MaHoSo = @MaHoSo
	end try	
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
commit
go






 ----------------------------------------Nhân Viên---------------------------------------
 --Danh sách nhân viên
 create
 --alter
  proc sp_DanhSachNhanVien
 as
 begin transaction
	begin try
		select nv.MaNV,Ten,DiaChi,CMND,SoDienThoai,NgaySinh,l.TenLoai,BangCap,Hinh,nv.MoTa,k.TenKhoa,GioiTinh,TenDangNhap,MatKhau,l.MaLoaiNV from NhanVien nv, Khoa k, LoaiNhanVien l where nv.MaKhoa = k.MaKhoa and nv.MaLoaiNV = l.MaLoaiNV
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
 go
 
 -- Thêm nhân viên
create 
--alter
proc sp_ThemNhanVien
@Ten nvarchar(Max),
@DiaChi nvarchar(Max),
@CMND nchar(10),
@SoDienThoai nvarchar(12),
@NgaySinh date,
@MaLoaiNV nchar(10) ,
@BangCap nvarchar(50),
@Hinh nvarchar(max), 
@MoTa nvarchar(max),
@MaKhoa nchar(10),
@GioiTinh int,
@TenDangNhap nvarchar(50),
@MatKhau nvarchar(50)
as
 begin transaction
	begin try
		declare @ma_next nchar(10) -- max 7 ki tu
		declare @max int
		select @max = count(MaNV) + 1 -- MANV ke tiep dang 'CVxxxxx'
		from dbo.NhanVien
		where  MaNV like 'NV%'
		-- tao maloaive ke tiep
		if(@max >0 and @max <10)
			set @ma_next = 'NV'+ right( '00' + cast(@max as nchar(10)) , 5) 
		if(@max >=10 and @max <100)
			set @ma_next = 'NV'+ right( '0' + cast(@max as nchar(10)) , 5)
		if(@max >=100 and @max <1000)
			set @ma_next = 'NV'+ right( '' + cast(@max as nchar(10)) , 5)
		-- kiem ta ton tai
		while(exists(select MaNV from dbo.NhanVien where MaNV= @ma_next))
		begin
		set @max = @max + 1
		set @ma_next = 'NV'+ right( '0' + cast(@max as nchar(10)), 5)
		end
		insert into NhanVien values(@ma_next,@Ten,@DiaChi,@CMND,@SoDienThoai,@NgaySinh,@MaLoaiNV,
		@BangCap,@Hinh,@MoTa,@MaKhoa,@GioiTinh,@TenDangNhap,@MatKhau)
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
go

--Cập nhật nhân viên
create 
--alter
proc sp_CapNhatNhanVien
@MaNV nchar(10),
@Ten nvarchar(Max),
@DiaChi nvarchar(Max),
@CMND nchar(10),
@SoDienThoai nvarchar(12),
@NgaySinh date,
@MaLoaiNV nchar(10) ,
@BangCap nvarchar(50),
@Hinh nvarchar(max), 
@MoTa nvarchar(max),
@MaKhoa nchar(10),
@GioiTinh int,
@TenDangNhap nvarchar(50),
@MatKhau nvarchar(50)
as
begin transaction
	begin try
		update NhanVien set Ten=@Ten,DiaChi=@DiaChi,CMND=@CMND,SoDienThoai=@SoDienThoai,
		NgaySinh = @NgaySinh,MaLoaiNV=@MaLoaiNV,BangCap=@BangCap,Hinh=@Hinh,
		MoTa=@MoTa,MaKhoa=@MaKhoa,GioiTinh=@GioiTinh,TenDangNhap=@TenDangNhap,MatKhau=@MatKhau
	    where MaNV = @MaNV
	end try	
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
commit
go

-- Xóa nhân viên
create procedure sp_XoaNhanVien
@MaNV nchar(10)
as
begin transaction
	begin try
		delete NhanVien where MaNV = @MaNV
	end try	
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
commit
go

---Kiểm tra đăng nhập
create
--alter
 proc sp_ktDangNhap
@TenDangNhap nvarchar(50),
@MatKhau nvarchar(50)
as
begin tran
	begin try
		select TenDangNhap,MatKhau,MaKhoa,MaNV,Ten from NhanVien where TenDangNhap=@TenDangNhap and MatKhau=@MatKhau
	end try
	begin catch			
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
commit
go




